from dbutils import db_exists, dbuser_exists

def create_dbuser(dbengine, dbuser, password):
    print "creating user %s"%dbuser
    conn = dbengine.connect()
    conn.execute("commit")
    conn.execute("create user %s with superuser password '%s'"%(dbuser, password))
    conn.execute("commit") # create database cannot be within a transaction
    conn.close()

def drop_database(dbengine, dbname):
    conn = dbengine.connect()
    conn.execute("commit")
    print "dropping database %s"%dbname
    conn.execute("drop database %s"%dbname)
    conn.execute("commit")
    conn.close()

def create_database(dbengine, dbname, platform):
    #from dbfunctions import create_drop_constraint_if_exists_function, create_or_replace_plpgsql, create_decode_genotype_function
    conn = dbengine.connect()
    conn.execute("commit")
    print "creating database %s"%dbname
    conn.execute("create database %s"%dbname)
    conn.execute("COMMENT ON DATABASE %s IS '%s'"%(dbname, platform))
    #conn.execute(create_or_replace_plpgsql)
    #conn.execute(create_drop_constraint_if_exists_function)
    #conn.execute(create_decode_genotype_function)
    conn.execute("commit")
    conn.close()

def create_seq(dbengine, dbname):
    conn = dbengine.connect()
    print "creating schema 'seq'"
    conn.execute("create schema seq")
    conn.close()

def init_db(dbname, platform, recreate):
    """Script to create PostgreSQL database

    Assumptions:
    1. psql has been installed
    2. PostgreSQL 8.4 or newer is installed and running on port 'portnum'.
    """
    import os, sqlalchemy, sys
    if platform != 'affy6' and platform != 'illumina':
        sys.exit("unknown platform %s"%platform)
    usr = os.popen('whoami').read().strip()
    from utils import create_engine_wrapper, get_conf, pg_url
    conf = get_conf()
    dbuser = conf["dbuser"]
    password = conf["password"]
    portnum = conf["portnum"]
    dbstring_usr = "%s://%s@:%s/postgres"%(pg_url(), usr, portnum)
    db_usr = create_engine_wrapper(dbstring_usr)
    dbstring = "%s://%s:%s@localhost:%s/postgres"%(pg_url(), dbuser, password, portnum)
    db = create_engine_wrapper(dbstring)
    dbstring_dbname = "%s://%s:%s@localhost:%s/%s"%(pg_url(), dbuser, password, portnum, dbname)
    db_dbname = create_engine_wrapper(dbstring_dbname)
    try:
        from utils import create_engine_wrapper
        conn = db_usr.connect()
        conn.close()
    except sqlalchemy.exc.OperationalError, e:
        if "Ident authentication failed for user" in e.args[0]:
            print "database user %s does not exist, creating..."%usr
            print "Enter sudo password at prompt ..."
            os.system('sudo su postgres -c "createuser -s %s"' % usr)
        else:
            raise

    # create dbuser
    if dbuser_exists(db_usr, dbuser):
        print "user %s already exists, skipping creation"%dbuser
    else:
        create_dbuser(db_usr, dbuser, password)

    # create database
    if not db_exists(db, dbname):
        create_database(db, dbname, platform)
        create_seq(db_dbname, dbname)
    else:
        if recreate is True:
            drop_database(db, dbname)
            create_database(db, dbname, platform)
            create_seq(db_dbname, dbname)
        elif recreate is False:
            print "database %s exists - not recreating..."%dbname
        else:
            yn = raw_input("%s exists - delete and recreate? (Answer y or n) "%dbname)
            if yn.lower().startswith('y'):
                drop_database(db, dbname)
                create_database(db, dbname, platform)
                create_seq(db_dbname, dbname)
            else:
                print "database %s exists - not recreating..."%dbname

if __name__ == '__main__':
    import sys, traceback
    from optparse import OptionParser
    usage = "usage: %prog [-n -r] dbname platform"
    parser = OptionParser(usage=usage)
    parser.add_option("-y", "--recreate", action="store_true", dest="recreate", help="recreate")
    parser.add_option("-n", "--no-recreate", action="store_false", dest="recreate", help="do not recreate")
    (options, args) = parser.parse_args()
    if len(args) != 2:
        print "incorrect number of arguments"
        parser.print_help()
        sys.exit(1)

    db = args[0]
    platform = args[1]
    init_db(db, platform, options.recreate)
